package pathways.Database.Implementation;

/**
 * Title:        Pathways
 * Description:  Pure Java version of the Pathways Project.
 * Copyright:    Copyright (c) 2002
 * Company:      Ants Technologies
 * @author Veejay Sani
 * @version 1.0
 */

import java.sql.ResultSet;
import java.sql.SQLException;

import pathways.Database.Interfaces.IDBConnection;
import pathways.util.Debug.CDebug;

public class CDBSelect
{

  public CDBSelect(IDBConnection connection)
  {
    conn = connection;
  }
  
  public ResultSet getEnzymesBySubstrateAndProduct(long substrateID, long productID, int[] excludeRxnIDs)
  {
    String sql = "";
    boolean whereAdded = false;
    
    sql = "SELECT DISTINCT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + "," + CDBFields.keywords();
    sql = sql + " FROM " + CDBTables.compounds() + " INNER JOIN " + CDBTables.reactions();
    sql = sql + " ON ";
    sql = sql + CDBFields.compoundID() + " = " + CDBFields.enzymeID();
    
    if (substrateID > 0)
    {
      sql = sql + " WHERE ";
      sql = sql + CDBFields.substrateID() + " = " + substrateID;
      whereAdded = true;
    }
    
    if (productID > 0)
    {
      if (whereAdded)
         sql = sql + " AND ";
      else
         sql = sql + " WHERE ";
      
      sql = sql + CDBFields.productID() + " = " + productID;   
    }
    
    for (int i = 0; i < excludeRxnIDs.length; i++)
    {
      if (excludeRxnIDs[i] > 0)
      {
	if (whereAdded)
	{
	   sql = sql + " AND ";
	}
	else
	{
	   sql = sql + " WHERE ";
	   whereAdded = true;
	}
	
	sql = sql + CDBFields.reactionID() + " <> " + excludeRxnIDs[i];   
      }
    }
    
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);
  }

  public ResultSet getProdcutsBySubstrateAndEnzyme(long substrateID, long enzymeID, int[] excludeRxnIDs)
  {
    String sql = "";
    boolean whereAdded = false;
    
    sql = "SELECT DISTINCT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + "," + CDBFields.keywords();
    sql = sql + " FROM " + CDBTables.compounds() + " INNER JOIN " + CDBTables.reactions();
    sql = sql + " ON ";
    sql = sql + CDBFields.compoundID() + " = " + CDBFields.productID();
    
    if (substrateID > 0)
    {
      sql = sql + " WHERE ";
      sql = sql + CDBFields.substrateID() + " = " + substrateID;
      whereAdded = true;
    }
    
    if (enzymeID > 0)
    {
      if (whereAdded)
         sql = sql + " AND ";
      else
         sql = sql + " WHERE ";
      
      sql = sql + CDBFields.enzymeID() + " = " + enzymeID;   
    }
    
    for (int i = 0; i < excludeRxnIDs.length; i++)
    {
      if (excludeRxnIDs[i] > 0)
      {
	if (whereAdded)
	{
	   sql = sql + " AND ";
	}
	else
	{
	   sql = sql + " WHERE ";
	   whereAdded = true;
	}
	
	sql = sql + CDBFields.reactionID() + " <> " + excludeRxnIDs[i];   
      }
    }
    
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);
  }

  public ResultSet getSubstratesByProductAndEnzyme(long productID, long enzymeID)
  {
    String sql = "";
    boolean whereAdded = false;
    
    sql = "SELECT DISTINCT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + "," + CDBFields.keywords();
    sql = sql + " FROM " + CDBTables.compounds() + " INNER JOIN " + CDBTables.reactions();
    sql = sql + " ON ";
    sql = sql + CDBFields.compoundID() + " = " + CDBFields.substrateID();
    
    if (productID > 0)
    {
      sql = sql + " WHERE ";
      sql = sql + CDBFields.productID() + " = " + productID;
      whereAdded = true;
    }
    
    if (enzymeID > 0)
    {
      if (whereAdded)
         sql = sql + " AND ";
      else
         sql = sql + " WHERE ";
         
      sql = sql + CDBFields.enzymeID() + " = " + enzymeID;   
    }
    
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getReaction(long substrateID, long enzymeID, long prodID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.substrateID() + " = " + substrateID;
    sql = sql + " AND ";
    sql = sql + CDBFields.enzymeID() + " = " + enzymeID;
    sql = sql + " AND ";
    sql = sql + CDBFields.productID() + " = " + prodID;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getCompound(long cmpdID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.compounds();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.compoundID() + " = " + cmpdID;
    
    return conn.runQuery(sql);     
  }
  
  public ResultSet getCompound(String cmpdName)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.compounds();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.compoundName() + " = '" + cmpdName + "'";
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getReaction(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    
    return conn.runQuery(sql);   
  }
  
  public ResultSet getCoSubstrates(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.coSubstrates();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    
    return conn.runQuery(sql);
  }

  public ResultSet getCoSubstrates2(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBTables.compounds() + "." + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + " FROM " + CDBTables.compounds() + " INNER JOIN ";
    sql = sql + CDBTables.coSubstrates() + " ON ";
    sql = sql + CDBTables.compounds() + "." + CDBFields.compoundID();
    sql = sql + " = ";
    sql = sql + CDBTables.coSubstrates() + "." + CDBFields.compoundID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getCoProducts(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.coProducts();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    
    return conn.runQuery(sql);
  }

  public ResultSet getCoProducts2(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBTables.compounds() + "." + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + " FROM " + CDBTables.compounds() + " INNER JOIN ";
    sql = sql + CDBTables.coProducts() + " ON ";
    sql = sql + CDBTables.compounds() + "." + CDBFields.compoundID();
    sql = sql + " = ";
    sql = sql + CDBTables.coProducts() + "." + CDBFields.compoundID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getCompoundsMatchingName(String cmpd)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + " FROM " + CDBTables.compounds();
    sql = sql + " WHERE ";
    //sql = sql + CDBFields.compoundName() + " LIKE('%" + cmpd + "%')";
    // sql = sql + " ORDER BY " + CDBFields.compoundName();
    sql = sql + CDBFields.keywords() + " LIKE('%" + cmpd + "%')";
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);
  }

  public ResultSet getCompoundsMatchingName(String cmpd, int filter)
  {
    // filter = 0 -> all compounds
    // filter = 1 -> substrates only
    // filter = 2 -> enzymes only
    // filter = 3 -> products
    // filter = 4 -> substrates & products
    
    String sql = "";
    
    sql = "SELECT DISTINCT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + "," + CDBFields.keywords();
    sql = sql + " FROM " + CDBTables.compounds();
    
    if (filter == 1)
    {
      sql = sql + " INNER JOIN " + CDBTables.reactions();
      sql = sql + " ON ";
      sql = sql + CDBFields.compoundID() + " = " + CDBFields.substrateID();
    }
    else if (filter == 2)
    {
      sql = sql + " INNER JOIN " + CDBTables.reactions();
      sql = sql + " ON ";
      sql = sql + CDBFields.compoundID() + " = " + CDBFields.enzymeID();
    }
    else if (filter == 3)
    {
      sql = sql + " INNER JOIN " + CDBTables.reactions();
      sql = sql + " ON ";
      sql = sql + CDBFields.compoundID() + " = " + CDBFields.productID();
    }
    else if (filter == 4)
    {
      sql = sql + " INNER JOIN " + CDBTables.reactions();
      sql = sql + " ON ";
      sql = sql + CDBFields.compoundID() + " = " + CDBFields.substrateID();
      sql = sql + " OR ";
      sql = sql + CDBFields.compoundID() + " = " + CDBFields.productID();
    }
    
    sql = sql + " WHERE ";
    //sql = sql + CDBFields.compoundName() + " LIKE('%" + cmpd + "%')";
    //sql = sql + " ORDER BY " + CDBFields.compoundName();
    sql = sql + CDBFields.keywords() + " LIKE('%" + cmpd + "%')";
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);
  }
  
  public long getAutoNumber(long rxnID, long subID, long enyzmeID, long prodID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.autoNumber() + " FROM " + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    sql = sql + " AND ";
    sql = sql + CDBFields.substrateID() + " = " + subID;
    sql = sql + " AND ";
    sql = sql + CDBFields.enzymeID() + " = " + enyzmeID;
    sql = sql + " AND ";
    sql = sql + CDBFields.productID() + " = " + prodID;
    
    ResultSet rs = conn.runQuery(sql);
    long autoNumber = 0;
    
    try {
	rs.next();
        autoNumber = rs.getLong(1);
    } catch(SQLException e) {
      CDebug.println(this,"getAutoNumber() Exception: " + e.toString());
    }
    
    return autoNumber;    
  }
  
  public ResultSet getTraces(long rxnID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.substrateID() + "," + CDBFields.productID() + "," + CDBFields.from() + "," + CDBFields.to();
    sql = sql + " FROM " + CDBTables.atomTraces();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;     
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getTrace(long rxnID, long subID, long prodID, int from)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.to() + " FROM " + CDBTables.atomTraces();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    sql = sql + " AND ";
    sql = sql + CDBFields.substrateID() + " = " + subID;
    sql = sql + " AND ";
    sql = sql + CDBFields.productID() + " = " + prodID;
    sql = sql + " AND ";
    sql = sql + CDBFields.from() + " = " + from;
    
    return conn.runQuery(sql);
  }
  
  private ResultSet selectStar(String table)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + table;
    
    return conn.runQuery(sql);  
  }

  private ResultSet selectStar(String table, String field, int id)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + table;
    sql = sql + " WHERE ";
    sql = sql + field + " = '" + id + "'";
    
    return conn.runQuery(sql);  
  }
  
  public ResultSet getTissues()
  {
    return selectStar(CDBTables.tissues());
  }

  public ResultSet getOrgans()
  {
    return selectStar(CDBTables.organs());
  }

  public ResultSet getCells()
  {
    return selectStar(CDBTables.cells());
  }

  public ResultSet getSubCellSubs()
  {
    return selectStar(CDBTables.subCellularSubs());
  }

  public ResultSet getSubCellProds()
  {
    return selectStar(CDBTables.subCellularProds());
  }

  public ResultSet getTissue(int id)
  {
    return selectStar(CDBTables.tissues(), CDBFields.tissueID(), id);
  }

  public ResultSet getOrgan(int id)
  {
    return selectStar(CDBTables.organs(), CDBFields.organID(), id);
  }

  public ResultSet getCell(int id)
  {
    return selectStar(CDBTables.cells(), CDBFields.cellID(), id);
  }

  public ResultSet getSubCellSub(int id)
  {
    return selectStar(CDBTables.subCellularSubs(), CDBFields.subCellSubID(), id);
  }

  public ResultSet getSubCellProd(int id)
  {
    return selectStar(CDBTables.subCellularProds(), CDBFields.subCellProdID(), id);
  }
  
  public ResultSet getInstantiatedData(int dataID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.instantiatedData();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.dataID() + " = " + dataID;
    
    return conn.runQuery(sql);  
  }
 
  public long getInstantiatedDataID(int fieldID, String value, long instRxnID, long userID, String ref)
  {
    String sql = "";
    long id = 0;
    
    sql = "Select " + CDBFields.dataID() + " FROM " + CDBTables.instantiatedData();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.fieldID() + " = " + fieldID;
    sql = sql + " AND ";
    sql = sql + CDBFields.value() + " = '" + value + "'";
    sql = sql + " AND ";
    sql = sql + CDBFields.instantiatedRxnID() + " = " + instRxnID;
    sql = sql + " AND ";
    sql = sql + CDBFields.userID() + " = " + userID;
    sql = sql + " AND ";
    sql = sql + CDBFields.reference() + " = '" + ref + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
      {
        id = rs.getInt(1);   
      }
    } catch (SQLException sqle) {
      CDebug.println(this,"getInstantiatedDataID() Exception: " + sqle.toString());
    }
    
    return id; 
  } 
  
  public ResultSet getInstantiation(long instantiatedRxnID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.instantiatedReactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.instantiatedRxnID() + " = " + instantiatedRxnID;
    
    return conn.runQuery(sql);  
  }
  
  public ResultSet getSpecies(int taxid)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.taxonomyNames();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.taxID() + " = " + taxid;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getSpace(int spaceID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.space();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.spaceID() + " = " + spaceID;
    
    return conn.runQuery(sql);  
  }
  
  public int getSpace(int tissueID, int organID, int cellID, int subCellSubID, int subCellProdID)
  {
    String sql = "";
    int id = 0;
       
    sql = "SELECT " + CDBFields.spaceID() + " FROM " + CDBTables.space();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.tissueID() + " = " + tissueID;
    sql = sql + " AND ";
    sql = sql + CDBFields.organID() + " = " + organID;
    sql = sql + " AND ";
    sql = sql + CDBFields.cellID() + " = " + cellID;
    sql = sql + " AND ";
    sql = sql + CDBFields.subCellSubID() + " = " + subCellSubID;
    sql = sql + " AND ";
    sql = sql + CDBFields.subCellProdID() + " = " + subCellProdID;
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
         id = rs.getInt(1);
    } catch (SQLException sqle) {
      CDebug.println(this, "getSpace() Exception: " + sqle.toString()); 
    }  
    
    return id;
  }
  
  public ResultSet getInstantiationData(int instRxnID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBTables.dataFields() + "." + CDBFields.fieldID() + ",";
    sql = sql + CDBTables.dataFields() + "." + CDBFields.fieldName() + ",";
    sql = sql + CDBFields.dataID() + ",";
    sql = sql + CDBFields.value();
    sql = sql + " FROM " + CDBTables.instantiatedData();
    sql = sql + " RIGHT OUTER JOIN ";
    sql = sql + CDBTables.dataFields();
    sql = sql + " ON ";
    sql = sql + CDBTables.instantiatedData() + "." + CDBFields.fieldID();
    sql = sql + " = ";
    sql = sql + CDBTables.dataFields() + "." + CDBFields.fieldID();
    sql = sql + " WHERE ";
    sql = sql + "(";
    sql = sql + CDBFields.instantiatedRxnID() + " = " + instRxnID;
    sql = sql + " OR ";
    sql = sql + CDBFields.instantiatedRxnID() + " IS NULL";
    sql = sql + ")";
    
    return conn.runQuery(sql);  
  }
  
  public int getInstantiationID(int spaceID, int reactionID, int taxID)
  {
    String sql = "";
    int instID = 0;
    
    sql = "SELECT " + CDBFields.instantiatedRxnID() + " FROM ";
    sql = sql + CDBTables.instantiatedReactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.spaceID() + " = " + spaceID;
    sql = sql + " AND ";
    sql = sql + CDBFields.reactionID() + " = " + reactionID;
    sql = sql + " AND ";
    sql = sql + CDBFields.taxID() + " = " + taxID;
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
      {
        instID = rs.getInt(1);  
      }
    } catch (SQLException sqle) {
      CDebug.println(this, "getInstantiationID() Exception: " + sqle.toString()); 
    }
    
    return instID;
  }

  public int getInstantiationID(int spaceID, int reactionID, int taxID, String time)
  {
    String sql = "";
    int instID = 0;
    
    sql = "SELECT " + CDBFields.instantiatedRxnID() + " FROM ";
    sql = sql + CDBTables.instantiatedReactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.spaceID() + " = " + spaceID;
    sql = sql + " AND ";
    sql = sql + CDBFields.reactionID() + " = " + reactionID;
    sql = sql + " AND ";
    sql = sql + CDBFields.taxID() + " = " + taxID;
    sql = sql + " AND ";
    sql = sql + CDBFields.date() + " = '" + time + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
      {
        instID = rs.getInt(1);  
      }
    } catch (SQLException sqle) {
      CDebug.println(this, "getInstantiationID() Exception: " + sqle.toString()); 
    }
    
    return instID;
  }
  
  public ResultSet getDataFields()
  {
    return this.selectStar(CDBTables.dataFields());
  }
  
  public ResultSet getDataField(int id)
  {
    return this.selectStar(CDBTables.dataFields(),CDBFields.fieldID(),id);  
  }
  
  public ResultSet getSpeciesMatchingName(String name)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.taxID() + "," + CDBFields.speciesName();
    sql = sql + " FROM " + CDBTables.taxonomyNames();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.speciesName() + " LIKE('" + name + "%')";
    
    return conn.runQuery(sql);  
  }
  
  public String getEcNumPartDescription(String partEcnum)
  {
    // ECNUM has various parts so supply a part and 
    // get its description
    
    String sql = "";
    String description = "";
    
    sql = "SELECT " + CDBFields.ecNumDescription() + " FROM ";
    sql = sql + CDBTables.ecnumDefs();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.ECNO() + " = '" + partEcnum + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
         description = rs.getString(1);
    } catch (SQLException sqle) {
      CDebug.println(this, "getEcNumPartDescription() Exception: " + sqle.toString()); 
    }
    
    return description;
  }
  
  public boolean isEnzyme(int cmpdID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.enzymeID() + " FROM ";
    sql = sql + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.enzymeID() + " = " + cmpdID;
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      return rs.next();
    } catch (SQLException sqle) {
      CDebug.println(this, "isEnzyme() -- Exception: " + sqle.toString());
      return false; 
    }  
  }
  
  public ResultSet getReactionsBySubstrate(int subID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.reactionID() + "," + CDBFields.productID();
    sql = sql + " FROM " + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.substrateID() + " = " + subID;
    
    return conn.runQuery(sql);
  }
  
  public boolean isProdOfReaction(int rxnID, int prodID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.reactionID() + " FROM ";
    sql = sql + CDBTables.reactions();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    sql = sql + " AND ";
    sql = sql + CDBFields.productID() + " = " + prodID;
    
    ResultSet rs = conn.runQuery(sql);
    
    boolean lbFound = false;
    
    try {
        lbFound = rs.next();
        rs.close(); 
    } catch (Exception e) {}
    
    return lbFound;
  }
  
  public int getPathwayID(int userID, String pathwayName)
  {
    String sql = "";
    int id = 0;
    
    sql = "SELECT " + CDBFields.pathwayID() + " FROM " + CDBTables.pathwayID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.userID() + " = " + userID;
    sql = sql + " AND ";
    sql = sql + CDBFields.pathwayName() + " = '" + pathwayName + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
      {
        id = rs.getInt(1);
        rs.close();     
      }
    } catch (SQLException sqle) {
       CDebug.println(this, "getPathwayID() SQLException: " + sqle.toString());
    }
    
    return id;
  }
  
  public ResultSet getPathway(int pathwayID)
  {
    String sql = "";
    
    sql = "SELECT * FROM " + CDBTables.pathway();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.pathwayID() + " = " + pathwayID;
    
    return conn.runQuery(sql);
  }
  
  public boolean doesPathwayExist(String pathwayName)
  {
    String sql = "";
    boolean lbExists = false;
    
    sql = "SELECT " + CDBFields.pathwayName() + " FROM ";
    sql = sql + CDBTables.pathwayID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.pathwayName() + " = '" + pathwayName + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      lbExists = rs.next();
      rs.close();
    } catch(SQLException sqle) {
      lbExists = false;
      CDebug.println(this,"doesPathwayExist() SQLException: " + sqle.toString());
    }
    
    return lbExists;
  }

  public ResultSet getPathwayConnectionPointBranches(int pathwayID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.substrateID() + ",";
    sql = sql + CDBFields.connectionPoint() + ",";
    sql = sql + CDBFields.reactionID() + " FROM ";
    sql = sql + CDBTables.pathwayConnectionBranches();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.pathwayID() + " = " + pathwayID;
    
    return conn.runQuery(sql); 
  }
  
  public ResultSet getPathwaySubstratesAsEnzymes(int pathwayID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.substrateID() + ",";
    sql = sql + CDBFields.reactionID() + ",";
    sql = sql + CDBFields.fromPoint() + ",";
    sql = sql + CDBFields.toPoint();
    sql = sql + " FROM " + CDBTables.pathwaySubstratesAsEnzymes();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.pathwayID() + " = " + pathwayID;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getPathwaysByLikeNames(String pathwayName)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.pathwayID() + ",";
    sql = sql + CDBFields.pathwayName();
    sql = sql + " FROM " + CDBTables.pathwayID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.pathwayName();
    sql = sql + " LIKE('%" + pathwayName + "%')";
    
    return conn.runQuery(sql);
  }

  public ResultSet getPathwaysBySubstrateID(int subID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBTables.pathway() + "." + CDBFields.pathwayID() + ",";
    sql = sql + CDBFields.pathwayName();
    sql = sql + " FROM " + CDBTables.pathwayID();
    sql = sql + " INNER JOIN ";
    sql = sql + CDBTables.pathway();
    sql = sql + " ON ";
    sql = sql + CDBTables.pathway() + "." + CDBFields.pathwayID();
    sql = sql + " = ";
    sql = sql + CDBTables.pathwayID() + "." + CDBFields.pathwayID();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.substrateID() + " = " + subID;
    
    return conn.runQuery(sql);
  }
  
  public ResultSet getInhibitors(int rxnID)
  {
    String sql = "";
    
    sql = "SELECT " + CDBFields.autoNumber() + "," + CDBFields.inhibitor();
    sql = sql + " FROM " + CDBTables.inhibitors();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.reactionID() + " = " + rxnID;
    sql = sql + " ORDER BY " + CDBFields.inhibitor();
    
    return conn.runQuery(sql); 
  }
  
  public int getInhibitor(String inhibitor)
  {
    String sql = "";
    int id = 0;
    
    sql = "SELECT " + CDBFields.autoNumber();
    sql = sql + " FROM " + CDBTables.inhibitors();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.inhibitor() + " = '" + inhibitor + "'";
    
    ResultSet rs = conn.runQuery(sql);
    
    try {
      if (rs.next())
	id = rs.getInt(1); 
    } catch (SQLException sqle) {
      CDebug.println(this,"getInhibitor() Exception: " + sqle.toString());
      id = 0; 
    }
    
    return id;
  }
  
  public ResultSet getSynonyms(int CmpdID, String CmpdName)
  {
    // gets synonyms for CmpdID excluding CmpdName 
    String sql = "";
    
    sql = "SELECT " + CDBFields.compoundID() + "," + CDBFields.compoundName();
    sql = sql + " FROM " + CDBTables.compounds();
    sql = sql + " WHERE ";
    sql = sql + CDBFields.compoundID() + " = " + CmpdID;
    sql = sql + " AND ";
    sql = sql + CDBFields.compoundName() + " <> '" + CmpdName + "'";
    sql = sql + " ORDER BY " + CDBFields.keywords();
    
    return conn.runQuery(sql);  
  }
    
  private IDBConnection conn;
}
